﻿"""
机台校准数据计算系统
"""
# 导入系统模块
import os
import uuid
import json
import pyodbc
from datetime import datetime
from flask import Flask, render_template, url_for, session, request, redirect, jsonify
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired, Length
from flask_wtf.file import FileField, FileRequired, FileAllowed

# 导入自制模块
from sortingdataprocess import get_test_data, get_cf, cal_end_data, get_item_code, get_ID
from weblog import get_logger
from sqlalchemy import create_engine
from firstcoef import first_coef
from lastcoef import last_coef

# 初始化日志功能，启动日志记录
logger = get_logger(__name__)
# logger.info("....")

# Flask实例化，加密Cookie
app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'secret string')
# 删除Jinja2语句中的空格和制表符
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
# 上传附件最大10Mb上限
app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024

# 所有机台的IP地址
# ip_dict = {'192.168.30.101': 1, '192.168.30.57': 2, '192.168.30.148': 3, '192.168.30.198': 4, '192.168.30.134': 5,
#            '192.168.30.195': 6, '192.168.30.16': 7, '192.168.30.177': 8, '192.168.30.151': 9, '192.168.30.10': 10,
#            '192.168.30.66': 11, '192.168.30.83': 12, '192.168.30.196': 13, '192.168.20.219': 14, '192.168.20.105': 15,
#            '192.168.30.108': 16, '192.168.30.3': 17, '192.168.30.179': 18, '192.168.60.98': 19, '192.168.30.45': 20,
#            '192.168.30.72': 21, '192.168.60.183': 22, '192.168.30.154': 23, '192.168.30.48':24 , '192.168.30.27':25,
#            '192.168.30.144': 26, '192.168.30.118':27, '192.168.30.180':28}
# ip_dict = {'192.168.30.57': 2, '127.0.0.1': 2, '192.168.30.3': 17}

f1 = open("./Config.json", encoding='utf-8')
json_info = json.load(f1)
ip_list = json_info.keys()
f1.close()


# 定义工单下载输入界面表单类，设置相关验证要求
class Form1(FlaskForm):
    item_code = StringField('工单号', validators=[DataRequired(), Length(11, 15)])
    excel_data = FileField('附件', validators=[FileRequired(), FileAllowed(['csv', 'xls', 'xlsx'])])
    submit = SubmitField('计算')


class Form2(FlaskForm):
    item_code = StringField('工单号', validators=[DataRequired(), Length(11, 15)])
    excel_data2 = FileField('附件', validators=[FileRequired(), FileAllowed(['csv', 'xls', 'xlsx'])])
    submit2 = SubmitField('获取校准系数')


class Form3(FlaskForm):
    excel_data3 = FileField('附件', validators=[FileRequired(), FileAllowed(['csv', 'xls', 'xlsx'])])
    submit3 = SubmitField('获取最终系数')


# 定义文件修改函数，新文件名为 工单号+批号+扩展名
def alter_filename(filename):
    ext = os.path.splitext(filename)[1]
    new_filename = uuid.uuid4().hex + ext
    return new_filename


temp_uploads = os.path.join('uploads', 'temp')
if os.path.exists(temp_uploads):
    pass
else:
    os.makedirs(temp_uploads)


# 注册/data路由和data视图函数
@app.route('/data', methods=['GET', 'POST'])
def data():
    machine_ip = request.remote_addr
    if machine_ip in ip_list:
        machine_name = json_info[machine_ip]['MachineName']
        # machine_label = json_info['Label']
        if "排测" in machine_name or "积分球" in machine_name:
            machine_id = json_info[machine_ip]['MachineID']
        else:
            machine_id = 999
    else:
        machine_name = ''
        machine_id = 999

    if machine_id == 999:
        logger.warn(f"{machine_ip} 非法访问 ！")
        return redirect(url_for('reject'))

    form1 = Form1()
    # 根据年月份自动创建上传文件目录
    uploadsDir = os.path.join('uploaddata', str(datetime.now().year), str(datetime.now().month))
    if os.path.exists(uploadsDir):
        pass
    else:
        os.makedirs(uploadsDir)
    app.config['UPLOAD_PATH'] = os.path.join(app.root_path, uploadsDir)

    if form1.validate_on_submit():
        # 获取上传文件信息
        f = form1.excel_data.data
        logger.info(f"{machine_id}号机台--未加系数--文件名：{f.filename}")
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        f.save(path)
        moid = form1.item_code.data
        item_code = get_item_code(moid)
        # 获取CF2，CF3数据
        CF2_pars = get_cf(item_code, 2)
        CF3_pars = get_cf(item_code, 3)
        test_data, error = get_test_data(path, machine_name, logger)

        # 判断CF2，CF3系数是存在
        if CF2_pars.empty:
            logger.error(f"{machine_id}号数据有误：{form1.item_code.data}工单--CF2系数有误！")
            return f'<p>CF2系数有误！请联系研发进行数据库更新。</p><br><a href="{url_for("data")}">返回</a>'
        elif CF3_pars.empty:
            logger.error(f"{machine_id}号数据有误：{form1.item_code.data}工单--CF3系数有误！")
            return f'<p>CF3系数有误！请联系研发进行数据库更新。</p><br><a href="{url_for("data")}">返回</a>'
        elif test_data.empty:
            logger.error(f"{machine_id}号数据有误：{form1.item_code.data}工单--上传数据读取错误！")
            return f'<p>上传数据读取错误！请截图反馈至实验室。<br>问题描述：{error}</p><br><a href="{url_for("data")}">返回</a>'
        else:
            # 附加CF2系数
            data_cf2 = cal_end_data(test_data, CF2_pars)
            # 附加CF3系数
            data_cf32 = cal_end_data(data_cf2, CF3_pars)
            data_cf32['LuminousFlux_lm'] = data_cf32['LuminousFlux_lm'].round(decimals=1)
            # 选取需要判定的数据
            data_to_excel = data_cf32.loc[:, ['TestNO', 'Current_mA', 'ForwardVoltage_V',
                                              'LuminousFlux_lm', 'Ra', 'R9', 'CIEx', 'CIEy']]
            logger.info(f'{machine_id}号数据加系数计算成功')
            return data_to_excel.to_html(header="true") + f'<br><a href="{url_for("data")}">返回</a>'

    return render_template('data.html', form1=form1)


# 注册/coef路由和coef视图函数
@app.route('/', methods=['GET', 'POST'])
def coef():
    engine = create_engine(
        "mssql+pyodbc://labins:labins966@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
        fast_executemany=True)
    # 创建两个数据库的连接,方便进行数据库的查询
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
                          'DATABASE=SortingDB;UID=peapp;PWD=peapp1')
    conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
                          'DATABASE=SortingDB;UID=qains;PWD=qains8100')
    # 根据IP地址判定并添加MachineID，服务器ID为0，非分光机台为100
    machine_ip = request.remote_addr
    if machine_ip in ip_list:
        machine_name = json_info[machine_ip]['MachineName']
        machine_label = json_info[machine_ip]['Label']
        # machine_label = json_info['Label']
        if "排测" in machine_name or "积分球" in machine_name:
            machine_id = json_info[machine_ip]['MachineID']
        else:
            machine_id = 999
    else:
        machine_name = ''
        machine_label = ''
        machine_id = 999

    if machine_id == 999:
        logger.warn(f"{machine_ip} 非法访问 ！")
        return redirect(url_for('reject'))

    form2 = Form2()
    form3 = Form3()
    # 步骤一：计算校准系数
    if form2.validate_on_submit():
        # 获取上传文件信息
        f = form2.excel_data2.data
        logger.info(f"{machine_id}号机台--执行校准系数计算")
        logger.info(f"{machine_id}号机台--校准系数--文件名：{f.filename}")
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        StandardPartID = get_ID(get_item_code(form2.item_code.data))
        if not StandardPartID:
            return f"<h2 style='color: red'>校准异常！</h2>" \
                   f"<p>未找到工单对应的标准件型号，请检查工单号是否正确！！</p> " \
                   + f'<br><a href="{url_for("coef")}">返回</a>'
        f.save(path)
        form2_infos = {'MoID': form2.item_code.data,
                       'StandardPartID': StandardPartID,
                       'MachineID': machine_id, 'temp_path': path,
                       'UploadOriginalFileName': f.filename, }

        flash_result, coef_data, lotID2, flag2, NG_dict = first_coef(form2_infos, machine_name, cnxn, conn, engine,
                                                                     logger, machine_label)
        # print(flag2)
        if flag2 == 0:
            NG_dict['RecordTime'] = datetime.now()
            col_name = ''
            values = ''
            for k, v in NG_dict.items():
                col_name += f'{k},'
                values += f"\'{v}\',"
            # 数据库相关语句
            cursor1 = cnxn.cursor()
            i_sql = f'insert into CalibrationRecord ({col_name[:-1]}) values ({values[:-1]})'
            # print(i_sql)
            cursor1.execute(i_sql)
            try:
                cnxn.commit()
            except Exception as e:
                cnxn.rollback()
                logger.error(f'校准NG记录添加失败，异常:{e}')

            session['lotID2'] = None
            session['MoID'] = None
            session['MachineID'] = None
            session['StandardPartID'] = None
            session['coef_data'] = None
            return redirect(url_for('result', flag=0, MoID=form2_infos['MoID'], LotID=f"{lotID2}",
                                    Machine_Label='LAB-分光机-002', Status=0, RecordTime=datetime.now()))
        else:

            session['lotID2'] = lotID2
            session['MoID'] = form2_infos['MoID']
            session['MachineID'] = form2_infos['MachineID']
            session['StandardPartID'] = form2_infos['StandardPartID']
            session['coef_data'] = coef_data
            session['result'] = flash_result
            # return result.to_html(header=True)
            return redirect(url_for('result', flag=1, MoID=form2_infos['MoID'], LotID=f"{lotID2}",
                                    Machine_Label='LAB-分光机-002', Status=0, RecordTime=datetime.now()))

    if form3.validate_on_submit():
        # 获取上传文件信息
        f = form3.excel_data3.data
        logger.info(f"{machine_id}号机台--执行最终系数计算")
        logger.info(f"{machine_id}号机台--最终系数--文件名：{f.filename}")
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        f.save(path)

        form3_infos = {'MoID': session.get("MoID"), 'LotID2': session.get("lotID2"),
                       'MachineID': session.get("MachineID"), 'StandardPartID': session.get("StandardPartID"),
                       'temp_path': path, 'UploadOriginalFileName': f.filename,
                       'ProcessID': '3', 'coef_data': session.get("coef_data")}

        flash_result, lotID3, flag3, NG_dict = last_coef(form3_infos, machine_name, machine_label, cnxn, conn, engine, logger)
        if flag3 == 0:
            NG_dict['RecordTime'] = datetime.now()
            col_name = ''
            values = ''
            for k, v in NG_dict.items():
                if v is str:
                    v = v.replace("'", "").replace("\"", "")
                col_name += f'{k},'
                values += f"\'{v}\',"
            # 数据库相关语句
            cursor1 = cnxn.cursor()
            i_sql = f'insert into CalibrationRecord ({col_name[:-1]}) values ({values[:-1]})'
            # print(i_sql)
            cursor1.execute(i_sql)
            try:
                cnxn.commit()
            except Exception as e:
                cnxn.rollback()
                logger.error(f'校准NG记录添加失败，异常:{e}')

            return redirect(url_for('result', flag=0, MoID=session.get("MoID"),
                                    LotID=f"{session.get('lotID2')}-{lotID3}",
                                    Machine_Label='LAB-分光机-002', Status=0, RecordTime=datetime.now()))
        else:
            session['result'] = flash_result
            return redirect(url_for('result', flag=2, MoID=session.get("MoID"),
                                    LotID=f"{session.get('lotID2')}-{lotID3}",
                                    Machine_Label='LAB-分光机-002', Status=1, RecordTime=datetime.now()))

    return render_template('coef.html', form2=form2, form3=form3,
                           moid=session.get("MoID"))


# 注册/reject路由和reject视图函数
@app.route('/reject', methods=['GET'])
def reject():
    return render_template('reject.html')


# 注册/路由和delete视图函数
@app.route('/delete', methods=['GET', 'POST'])
def delete():
    session.clear()
    return redirect(url_for("cal"))


@app.route('/result/<flag>/<MoID>/<LotID>/<Machine_Label>/<Status>/<RecordTime>', methods=['GET', 'POST'])
def result(flag, MoID, LotID, Machine_Label, Status, RecordTime):
    connect1 = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31;'
                              'DATABASE=SortingDB;UID=peapp;PWD=peapp1')
    finaldict = {'MoID': MoID, 'LotID': LotID, 'Machine_Label': Machine_Label, 'Status': Status,
                 'RecordTime': RecordTime}
    res = session.get('result')
    res = json.loads(res)
    # 成功记录OK记录
    if flag == '2':
        col_name = ''
        values = ''
        for k, v in finaldict.items():
            col_name += f'{k},'
            values += f"\'{v}\',"
        # 数据库相关语句
        cursor1 = connect1.cursor()
        i_sql = f'insert into CalibrationRecord ({col_name[:-1]}) values ({values[:-1]})'
        # print(i_sql)
        cursor1.execute(i_sql)
        try:
            connect1.commit()
        except Exception as e:
            logger.error(f'数据库异常:{e}')
            return f'<h5>校准OK记录保存失败</h5><br><a href="{url_for("cal")}">返回</a>'
        else:
            connect1.close()
    # print(res)
    return render_template('result.html', d_html_text=res, flag=flag)
    # return res.to_html(header=True)


# 主程序入口
if __name__ == '__main__':
    app.run()
